﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CommonExtensions.ObjectExtensions;
using CommonExtensions.StringExtensions;
using DBMigrator.Core.DbObjects;
using DBMigrator.Core.DbObjects.Column;
using DBMigrator.Core.DbObjects.Data.Delete;
using DBMigrator.Core.DbObjects.Data.Insert;
using DBMigrator.Core.DbObjects.Data.Select;
using DBMigrator.Core.DbObjects.Data.Update;
using DBMigrator.Core.DbObjects.Data.What;
using DBMigrator.Core.DbObjects.Data.Where;
using DBMigrator.Core.DbObjects.Table;
using DBMigrator.Core.SqlGenerators;
using StructureMap;

namespace DBMigrator.Core.DBProviders
{
    public abstract class Provider : IProvider
    {
        protected IDbConnection Connection { get; private set; }
        protected IDbTransaction Transaction { get; private set; }

        public virtual void Initialize(string connectionString)
        {
            if(connectionString.IsBlank())
                throw new ArgumentNullException("connectionString");

            Connection = CreateConnection(connectionString);
            Connection.Open();
            Transaction = Connection.BeginTransaction();
        }

        protected abstract IDbConnection CreateConnection(string connectionString);

        protected virtual string GetSqlFor(IDbChange change)
        {
            return ObjectFactory.GetNamedInstance<ISqlGenerator>(change.SqlGenerationType.Name)
                .GenerateSqlFor(change);
        }

        public virtual bool DoesTableExist(string tableName)
        {
            if(tableName.IsBlank())
                throw new ArgumentNullException("tableName");

            var tableExists = ObjectFactory.With<string>(tableName).GetInstance<ITableExists>();

            return ExecuteScalar<int>(GetSqlFor(tableExists)) > 0;
        }

        public virtual void CreateTable(string tableName, Action<ICreateTable> tableSetup)
        {
            var table = ObjectFactory.With<string>(tableName).GetInstance<ICreateTable>();
            tableSetup(table);

            ExecuteNonQuery(GetSqlFor(table));
        }

        public virtual void CreateTableWithPrimaryKey(string tableName, string primaryKeyName,
                                                              Action<ICreateTable> tableSetup)
        {
            CreateTableWithPrimaryKey<int>(tableName, primaryKeyName, tableSetup);
        }

        public virtual void CreateTableWithPrimaryKey<TPrimaryKey>(string tableName, string primaryKeyName,
                                                                           Action<ICreateTable> tableSetup)
        {
            if (primaryKeyName.IsBlank())
                throw new ArgumentNullException("primaryKeyName");
            if (tableSetup.IsNull())
                throw new ArgumentNullException("tableSetup");

            CreateTable(tableName,
                        t =>
                        {
                            t.AddColumn<TPrimaryKey>(primaryKeyName).SetAsPrimaryKey();
                            tableSetup(t);
                        });
        }

        public virtual void AlterTable(string tableName, Action<IAlterTable> alterations)
        {
            if (tableName.IsBlank())
                throw new ArgumentNullException("tableName");
            if (alterations.IsNull())
                throw new ArgumentNullException("alterations");

            var table = ObjectFactory.With<string>(tableName).GetInstance<IAlterTable>();
            alterations(table);

            ExecuteNonQuery(GetSqlFor(table));
        }

        public virtual void DropTable(string tableName)
        {
            if (tableName.IsBlank())
                throw new ArgumentNullException("tableName");

            var table = ObjectFactory.With<string>(tableName).GetInstance<IDropTable>();

            ExecuteNonQuery(GetSqlFor(table));
        }

        public virtual IDataReader Select(Action<IWhat> what, Action<IFrom> from, Action<IWhere> where)
        {
            if (what.IsNull())
                throw new ArgumentNullException("what");
            if (from.IsNull())
                throw new ArgumentNullException("from");

            var select = ObjectFactory.GetInstance<ISelect>();
            what(select.What);
            from(select.From);
            
            if(!where.IsNull())
            {
                where(select.Where);
            }

            return ExecuteQuery(GetSqlFor(select));
        }

        public virtual IDataReader Select(Action<IWhat> what, Action<IFrom> from)
        {
            return Select(what, from, null);
        }

        public virtual IDataReader SelectAllFrom(Action<IFrom> from, Action<IWhere> where)
        {
            return Select(c => c.AllColumns(), from, where);
        }

        public virtual IDataReader SelectAllFrom(Action<IFrom> from)
        {
            return Select(c => c.AllColumns(), from);
        }

        public virtual IDataReader SelectAllFrom(string tableName, Action<IWhere> where)
        {
            if (tableName.IsBlank())
                throw new ArgumentNullException("tableName");

            return SelectAllFrom(f => f.Table(tableName), where);
        }

        public virtual IDataReader SelectAllFrom(string tableName)
        {
            return SelectAllFrom(f => f.Table(tableName), null);
        }

        public virtual int SelectCountFrom(string whatToCount, Action<IFrom> from, Action<IWhere> where)
        {
            if(whatToCount.IsBlank())
                throw new ArgumentNullException("whatToCount");
            if(from.IsNull())
                throw new ArgumentNullException("from");

            var selectCount = ObjectFactory.With<string>("columnName").GetInstance<ISelectCount>();
            from(selectCount.From);

            if(!where.IsNull())
            {
                where(selectCount.Where);
            }

            return ExecuteScalar<int>(GetSqlFor(selectCount));
        }

        public virtual int SelectCountFrom(string whatToCount, Action<IFrom> from)
        {
            return SelectCountFrom(whatToCount, from, null);
        }

        public virtual int SelectCountFrom(string whatToCount, string tableName, Action<IWhere> where)
        {
            return SelectCountFrom(whatToCount, f => f.Table(tableName), where);
        }

        public virtual int SelectCountFrom(string whatToCount, string tableName)
        {
            return SelectCountFrom(whatToCount, tableName, null);
        }

        public virtual int DeleteFrom(string tableName, Action<IWhere> where)
        {
            if (tableName.IsBlank())
                throw new ArgumentNullException("tableName");

            var delete = ObjectFactory.With<string>(tableName).GetInstance<IDelete>();

            if(!where.IsNull())
            {
                where(delete.Where);
            }

            return ExecuteNonQuery(GetSqlFor(delete));
        }

        public virtual int DeleteAllFrom(string tableName)
        {
            return DeleteFrom(tableName, null);
        }

        public virtual int InsertInto<T>(string tableName, IEnumerable<T> values)
        {
            if(tableName.IsBlank())
                throw new ArgumentNullException("tableName");
            if(values.IsNull())
                throw new ArgumentNullException("values");
            if(values.Count() == 0)
                throw new ArgumentException("values must have at least one value to insert", "values");

            var insert = ObjectFactory.With<string>(tableName).With(values).GetInstance<IInsert<T>>();

            return ExecuteNonQuery(GetSqlFor(insert));
        }

        public virtual int InsertInto<T>(string tableName, T value)
        {
            return InsertInto<T>(tableName, new List<T>{value});
        }

        public virtual int Update(string tableName, Action<ISet> set, Action<IWhere> where)
        {
            if (tableName.IsBlank())
                throw new ArgumentNullException("tableName");
            if (set.IsNull())
                throw new ArgumentNullException("set");

            var update = ObjectFactory.With<string>(tableName).GetInstance<IUpdate>();
            
            set(update.Set);

            if(!where.IsNull())
            {
                where(update.Where);
            }

            return ExecuteNonQuery(GetSqlFor(update));
        }

        public virtual int Update(string tableName, Action<ISet> set)
        {
            return Update(tableName, set, null);
        }

        public virtual int ExecuteNonQuery(string sql)
        {
            return BuildCommand(sql).ExecuteNonQuery();
        }

        public virtual IDataReader ExecuteQuery(string sql)
        {
            return BuildCommand(sql).ExecuteReader();
        }

        public virtual T ExecuteScalar<T>(string sql)
        {
            return (T)BuildCommand(sql).ExecuteScalar();
        }

        protected virtual IDbCommand BuildCommand(string sql)
        {
            IDbCommand command = Connection.CreateCommand();
            command.CommandText = sql;
            command.CommandType = CommandType.Text;
            command.Transaction = Transaction;
            return command;
        }

        public void Dispose()
        {
            if(!Transaction.IsNull())
            {
                Transaction.Commit();
                Transaction.Dispose();
            }

            if (!Connection.IsNull())
            {
                Connection.Dispose();
            }
        }
    }
}
